package com.ahui.convert;

import com.ahui.pojo.TableInfo;
import com.ahui.util.SqlUtil;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author : ph.li
 * @date : 2023/7/21 15:33
 */
@Component
public class ConvertOracleSql implements CommandLineRunner {

    @Override
    public void run(String... args) {
        File[] files =  getFiles();

        for (File file : files) {
            if (file.isFile() && file.getName().toLowerCase().endsWith(".sql")) {
                // 读取处理.sql文件，返回map
                List<TableInfo> tableInfos =  doSql(file.getAbsoluteFile());
                // 解析map成实体类
                createPojo(tableInfos);
                // 解析map成mapper
                createMapper(tableInfos);
                // 解析map成service
                createService(tableInfos);
                // 解析map成serviceImpl
                createServiceImpl(tableInfos);
                // 解析生成Controller
                createController(tableInfos);
            }
        }
    }

    // 获取文件夹.sql文件
    private File[] getFiles() {
//        String projectPath = new File(new File(System.getProperty("user.dir")).getParent()).getParent();

        String projectPath = System.getProperty("user.dir");
        System.out.println(projectPath);
        File folder  = new File(projectPath);
        return folder.listFiles();
    }

    // 创建ServiceImpl文件
    private void createServiceImpl(List<TableInfo> tableInfos) {
        for (TableInfo tableInfo : tableInfos) {
            String modelName = tableInfo.getModelName();
            String smallModelName = tableInfo.getSmallModelName();
            String serviceName = tableInfo.getServiceName();
            String mapperName = tableInfo.getMapperName();
            String smallMapperName = tableInfo.getSmallMapperName();
            String serviceImplName = tableInfo.getServiceImplName();
            String builder = "import com.hex.xchannel.common.BaseService;\n" +
                    "import lombok.extern.slf4j.Slf4j;\n" +
                    "import org.springframework.stereotype.Service;\n" +
                    "\n" +
                    "@Service\n" +
                    "@Slf4j\n" +
                    "public class " + serviceImplName + " " +
                    "extends BaseService<" + mapperName + ", " + modelName + "> " +
                    "implements " + serviceName + " {\n" +
                    "    @Resource\n" +
                    "    GFNextIdService gfNextIdService;\n" +
                    "\n" +
                    "    @Resource\n" +
                    "    " + mapperName +" "+ smallMapperName +";\n" +
                    "\n" +
                    "    @Override\n" +
                    "    public List<" + modelName + "> list(" + modelName + " " + smallModelName + ") {\n" +
                    "        return null;\n" +
                    "    }\n" +
                    "\n" +
                    "    @Override\n" +
                    "    public GridData page(" + modelName + " " + smallModelName + ", Page page) {\n" +
                    "        return null;\n" +
                    "    }\n" +
                    "\n" +
                    "    @Override\n" +
                    "    public " + modelName + " getById(String id) {\n" +
                    "        return null;\n" +
                    "    }\n" +
                    "\n" +
                    "    @Override\n" +
                    "    @Transactional(rollbackFor = Exception.class)\n" +
                    "    public " + modelName + " add(" + modelName + " " + smallModelName + ") {\n" +
                    "        return null;\n" +
                    "    }\n" +
                    "    \n" +
                    "\n" +
                    "    @Override\n" +
                    "    @Transactional(rollbackFor = Exception.class)\n" +
                    "    public void deleteByIds(List<String> ids) {\n" +
                    "\n" +
                    "    }\n" +
                    "}";

            createJavaFile(serviceImplName, builder);
        }
    }

    // 创建Service文件
    private void createService(List<TableInfo> tableInfos) {
        for (TableInfo tableInfo : tableInfos) {
            String modelName = tableInfo.getModelName();
            String smallModelName = tableInfo.getSmallModelName();
            String serviceName = tableInfo.getServiceName();
            String serviceImplName = tableInfo.getServiceImplName();
            String builder = "import com.hex.goframe.base.model.GridData;\n" +
                    "import com.hex.goframe.engine.model.Page;\n\n" +
                    "import java.util.List;\n" +
                    "public interface " + serviceImplName + " {\n" +
                    "    List<" + modelName + "> list(" + modelName + " " + smallModelName + ");\n" +
                    "\n" +
                    "    " + modelName + " add(" + modelName + " " + smallModelName + ");\n" +
                    "\n" +
                    "    void deleteByIds(List<String> ids);\n" +
                    "\n" +
                    "    GridData page(" + modelName + " " + smallModelName + ", Page page);\n" +
                    "\n" +
                    "    " + modelName + " getById(String id);\n" +
                    "}";
            createJavaFile(serviceName, builder);
        }
    }

    // 创建Mapper文件
    private void createMapper(List<TableInfo> tableInfos) {
        for (TableInfo tableInfo : tableInfos) {
            String modelName = tableInfo.getModelName();
            String mapperName = tableInfo.getMapperName();
            String builder = "import com.baomidou.mybatisplus.core.mapper.BaseMapper;\n" +
                    "import org.apache.ibatis.annotations.Mapper;\n\n" +

                    "@Mapper\n" +
                    "public interface " + mapperName + " extends BaseMapper<" + modelName + "> {\n" +
                    "}";
            createJavaFile(mapperName, builder);
        }
    }

    // 创建Controller文件
    private void createController(List<TableInfo> tableInfos){
        for (TableInfo tableInfo : tableInfos) {
            String modelName = tableInfo.getModelName();
            String smallModelName = tableInfo.getSmallModelName();
            String serviceName = tableInfo.getServiceName();
            String smallServiceName = tableInfo.getSmallServiceName();
            String controllerName = tableInfo.getControllerName();
            String tableComment = tableInfo.getTableComment();
            String builder = "import com.hex.goframe.base.model.GridData;\n" +
                    "import com.hex.goframe.engine.model.MessageResult;\n" +
                    "import com.hex.goframe.engine.model.Page;\n" +
                    "import io.swagger.annotations.Api;\n" +
                    "import io.swagger.annotations.ApiOperation;\n" +
                    "import io.swagger.annotations.ApiParam;\n" +
                    "import lombok.extern.slf4j.Slf4j;\n" +
                    "import org.springframework.web.bind.annotation.*;\n" +
                    "import javax.annotation.Resource;\n" +
                    "import java.util.List;\n\n" +
                    "@Api(tags = \"" + tableComment + "\")\n" +
                    "@Slf4j\n" +
                    "@RestController\n" +
                    "@RequestMapping(\"\")\n" +
                    "public class " + controllerName + " {\n\n" +
                    "    @Resource\n" +
                    "    " + serviceName + " " + smallServiceName + ";\n" +
                    "\n" +
                    "    @PostMapping(\"/list\")\n" +
                    "    @ApiOperation(value = \"" + tableComment + "列表\")\n" +
                    "    public MessageResult<List<" + modelName + ">> list(@RequestBody(required = false) " + modelName + " " + smallModelName + ") {\n" +
                    "        return new MessageResult<>(Boolean.TRUE, " + smallServiceName + ".list(" + smallModelName + "));\n" +
                    "    }\n" +
                    "\n" +
                    "    @PostMapping(\"/page\")\n" +
                    "    @ApiOperation(value = \"分页\")\n" +
                    "    public MessageResult<GridData> page(@ApiParam(\"" + tableComment + "\") " + modelName + " " + smallModelName + ",\n" +
                    "                                            @ApiParam(\"分页\") Page page) {\n" +
                    "        return new MessageResult<>(Boolean.TRUE, " + smallServiceName + ".page(" + smallModelName + ", page));\n" +
                    "    }\n" +
                    "\n" +
                    "\n" +
                    "    @PostMapping(\"/getById/{id}\")\n" +
                    "    @ApiOperation(value = \"根据ID获取详情\")\n" +
                    "    public MessageResult<" + modelName + "> getById(@PathVariable String id) {\n" +
                    "        return new MessageResult<>(Boolean.TRUE, " + smallServiceName + ".getById(id));\n" +
                    "    }\n" +
                    "\n" +
                    "    @PostMapping(\"/add\")\n" +
                    "    @ApiOperation(value = \"新增或编辑\")\n" +
                    "    public MessageResult<" + modelName + "> add(@RequestBody " + modelName + " " + smallModelName + ") {\n" +
                    "        return new MessageResult<>(Boolean.TRUE, " + tableInfo.getSmallServiceName() + ".add(" + tableInfo.getSmallModelName() + "));\n" +
                    "    }\n" +
                    "\n" +
                    "\n" +
                    "    @PostMapping(\"/deleteByIds\")\n" +
                    "    @ApiOperation(value = \"批量删除\")\n" +
                    "    public MessageResult<String> deleteByIds(@RequestBody List<String> ids) {\n" +
                    "        " + smallServiceName + ".deleteByIds(ids);\n" +
                    "        return new MessageResult<>(true);\n" +
                    "    }\n" +
                    "}";
            createJavaFile(tableInfo.getControllerName(), builder);
        }
    }

    // 创建实体类
    private void createPojo(List<TableInfo> tableInfos) {
        for (TableInfo tableInfo : tableInfos) {
            String modelName = tableInfo.getModelName();
            StringBuilder builder = new StringBuilder();
            builder.append("import com.baomidou.mybatisplus.annotation.TableField;\n" +
                            "import com.baomidou.mybatisplus.annotation.TableId;\n" +
                            "import com.baomidou.mybatisplus.annotation.TableName;\n" +
                            "import io.swagger.annotations.ApiModel;\n" +
                            "import io.swagger.annotations.ApiModelProperty;\n" +
                            "import lombok.Data;\n\n\n" +
                            "@Data\n" +
                            "@ApiModel(\"")
                    .append(tableInfo.getTableComment())
                    .append("\")\n").append("@TableName(\"")
                    .append(tableInfo.getTableName().toUpperCase())
                    .append("\")\n").append("public class ")
                    .append(modelName).append("{\n\n");

            // 打印主键信息
            if (StringUtils.hasLength(tableInfo.getPrimaryKeyName())) {
                builder.append("\t@ApiModelProperty(\"主键\")\n");
                builder.append("\t@TableId(\"")
                        .append(tableInfo.getPrimaryKeyName().toUpperCase())
                        .append("\")\n");
                builder.append("\tprivate ")
                        .append(tableInfo.getPrimaryKeyType())
                        .append(" ")
                        .append(SqlUtil.converSmalltHump(tableInfo.getPrimaryKeyName()))
                        .append(";\n\n");
            }

            for (Map.Entry<String, String> entry : tableInfo.getFieldsType().entrySet()) {
                String key = entry.getKey();
                String s = tableInfo.getFieldsComment().get(key);
                if (s == null) {
                    builder.append("\t@ApiModelProperty(\"\")\n");
                } else {
                    builder.append("\t@ApiModelProperty(\"")
                            .append(s)
                            .append("\")\n");
                }
                builder.append("\t@TableField(\"")
                        .append(entry.getKey().toUpperCase())
                        .append("\")\n");
                builder.append("\tprivate ")
                        .append(entry.getValue())
                        .append(" ")
                        .append(SqlUtil.converSmalltHump(entry.getKey()))
                        .append(";\n\n");
            }
            builder.append("}");
            createJavaFile(modelName, builder.toString());
        }


    }

    // 输出文件
    private void createJavaFile(String path,String str) {
        String projectPath = System.getProperty("user.dir");

        File file = new File(projectPath + "/"+ path +".java");
        FileOutputStream fos = null;
        // 判断文件是否存在，若不存在则创建文件
        if (!file.exists()) {
            try {
                file.createNewFile();

                // 将内容写入文件中
                fos = new FileOutputStream(file);
                fos.write(str.getBytes(StandardCharsets.UTF_8));
            } catch (IOException e) {
                throw new RuntimeException(e);
            } finally {
                if (fos != null){
                    try {
                        fos.close();
                    } catch (IOException e) {
                        throw new RuntimeException(e);
                    }
                }
            }
        }


    }



    // 获取转换后的表信息类集合
    private List<TableInfo> doSql(File file) {
        // 读取sql文件作为字符串
        String sqlStr = getSqlStr(file);
        List<String> bodyList = new ArrayList<>(); // create ... {}
        List<String> commentList = new ArrayList<>(); // comment ... ;
        Map<String,String> primaryKeyMap = new HashMap<>();
        List<TableInfo> tableInfos = new ArrayList<>();

        // 获取建表字符串列表
        getBodyStrList(sqlStr.toLowerCase(),bodyList);

        // 获取字段、表注释字符串列表
        getCommentList(sqlStr.toLowerCase().replace(" ",""),commentList);

        // 获取建表语句外的主键集合 [tableName,primaryKeyName]
        getPrimaryKeyMap(sqlStr.toLowerCase().replace(" ",""),primaryKeyMap);

        for (String s : bodyList) {
            TableInfo tableInfo = new TableInfo();

            // 设置表名
            int flagA = s.indexOf("(");
            String containTableName = s.substring(0, flagA);
            String tableName = containTableName
                    .replaceAll(" ", "")
                    .replace("createtable", "");
            tableInfo.setTableName(tableName);

            // 设置字段-类型map
            int flagB = s.lastIndexOf(")");
            String fieldsInfoStr = s.substring(flagA + 1, flagB);

            // 设置主键name
            if (fieldsInfoStr.indexOf("constraint") == 0) {
                int firstPrimaryFlag = fieldsInfoStr.indexOf("constraint") + 10;
                int lastPrimaryFlag = fieldsInfoStr.lastIndexOf("primarykey(");
                String primaryName = fieldsInfoStr.substring(firstPrimaryFlag, lastPrimaryFlag);
                tableInfo.setPrimaryKeyName(primaryName);
            }
            if (!CollectionUtils.isEmpty(primaryKeyMap) && !StringUtils.hasLength(tableInfo.getPrimaryKeyName())) {
                String primaryKeyName = primaryKeyMap.get(tableName);
                if (StringUtils.hasLength(primaryKeyName)) {
                    tableInfo.setPrimaryKeyName(primaryKeyName);
                }
            }

            String[] fieldsInfo = fieldsInfoStr.split(",");
            Map<String, String> fieldsTypeMap = new HashMap<>();
            for (String value : fieldsInfo) {
                if (value.contains("constraint")) {
                    continue;
                }
                String[] splitField = value.trim().split("\\s+");
                String fieldName = splitField[0];
                // 类型映射
                String fieldType = SqlUtil.typeMatching(splitField[1]);
                fieldsTypeMap.put(fieldName, fieldType);
                tableInfo.setFieldsType(fieldsTypeMap);
            }
            // 获取主键类型，去除主键列
            if (StringUtils.hasLength(tableInfo.getPrimaryKeyName())) {
                String primaryKeyType = tableInfo.getFieldsType().get(tableInfo.getPrimaryKeyName());
                tableInfo.setPrimaryKeyType(primaryKeyType);
                Map<String, String> finalFieldsTypeMap = tableInfo.getFieldsType();
                finalFieldsTypeMap.remove(tableInfo.getPrimaryKeyName());
                tableInfo.setFieldsType(finalFieldsTypeMap);
            }

            // 处理字段、表注释
            // 获取当前表的表注释
            List<String> filedCommentList = commentList.stream()
                    .filter(commentStr -> commentStr.contains(tableName))
                    .map(commentStr -> commentStr.replace(tableName, ""))
                    .map(commentStr -> commentStr.replace(".", ""))
                    .filter(commentStr -> commentStr.indexOf("-") != 0)
                    .collect(Collectors.toList());

            // 获取当前表的字段注释，去除  表名.
            List<String> tableCommentList = commentList.stream()
                    .filter(commentStr -> commentStr.contains(tableName))
                    .map(commentStr -> commentStr.replace(tableName, ""))
                    .map(commentStr -> commentStr.replace(".", ""))
                    .filter(commentStr -> commentStr.indexOf("-") == 0)
                    .collect(Collectors.toList());

            // 设置表注释
            if (!CollectionUtils.isEmpty(tableCommentList)) {
                tableInfo.setTableComment(tableCommentList.get(0).replace("is", "").replace("-",""));
            }

            // 设置字段-注释map
            Map<String, String> fieldsCommentMap = new HashMap<>();
            for (String currentComment : filedCommentList) {
                String[] fieldAndComment = currentComment.split("-");
                fieldsCommentMap.put(fieldAndComment[0], fieldAndComment[1]);
                tableInfo.setFieldsComment(fieldsCommentMap);
            }
            tableInfo.setControllerName(SqlUtil.convertHump(tableInfo.getTableName()) + "Controller");
            tableInfo.setServiceName(SqlUtil.convertHump(tableInfo.getTableName()) + "Service");
            tableInfo.setServiceImplName(SqlUtil.convertHump(tableInfo.getTableName()) + "ServiceImpl");
            tableInfo.setMapperName(SqlUtil.convertHump(tableInfo.getTableName()) + "Mapper");
            tableInfo.setModelName(SqlUtil.convertHump(tableInfo.getTableName()));
            tableInfo.setSmallMapperName(SqlUtil.converSmalltHump(tableInfo.getTableName()) + "Mapper");
            tableInfo.setSmallModelName(SqlUtil.converSmalltHump(tableInfo.getTableName()));
            tableInfo.setSmallServiceName(SqlUtil.converSmalltHump(tableInfo.getTableName()) + "Service");
            tableInfos.add(tableInfo);
        }

        return tableInfos;
    }

    // 获取建表语句外的主键集合 [tableName,primaryKeyName]
    private Map<String, String> getPrimaryKeyMap(String sqlStr, Map<String, String> primaryKeyMap) {
        int firstFlag = sqlStr.indexOf("alter");

        if (firstFlag == -1){
            return primaryKeyMap;
        }else {
            // 截取alter后面的字符串
            String temSqlStr = sqlStr.substring(firstFlag,sqlStr.length());
            int lastFlag = temSqlStr.indexOf(")");
            // 获取alter语句
            String alterSql = temSqlStr.substring(0, lastFlag);
            int nameFirstFlag = alterSql.indexOf("table") + 5;
            int nameLastFlag = alterSql.indexOf("addprimarykey(");
            // 获取表名
            String tableName = alterSql.substring(nameFirstFlag, nameLastFlag);
            int typeFistFlag = alterSql.lastIndexOf("(") + 1;
            // 获取主键名
            String idName = alterSql.substring(typeFistFlag, alterSql.length());
            primaryKeyMap.put(tableName,idName);
            return getPrimaryKeyMap(temSqlStr.substring(lastFlag+1,temSqlStr.length()),primaryKeyMap);
        }
    }

    // 获取注释sql集合
    private List<String> getCommentList(String sqlStr,List<String> commentList) {
        int firstFlag = sqlStr.indexOf("commenton");

        if (firstFlag == -1){
            return commentList;
        }else {
            // 获取comment on后面的sql
            String currentSqlStr = sqlStr.substring(firstFlag, sqlStr.length());
            int lastFlag = currentSqlStr.indexOf("';");


            // 获取第一个注释
            commentList.add((currentSqlStr.substring(0,lastFlag+2))
                    .replaceAll("commentoncolumn","")
                    .replaceAll("commentontable","")
                    .replaceFirst("is'","-")
                    .replaceFirst("is\"","-")
                    .replace("'","")
                    .replace("\"","")
                    .replace(";",""));
            currentSqlStr = currentSqlStr.substring(0,firstFlag) + currentSqlStr.substring(lastFlag+2,currentSqlStr.length());
            return getCommentList(currentSqlStr,commentList);
        }
    }

    // 获取建表sql集合
    private List<String> getBodyStrList(String sqlStr,List<String> bodyList) {
//        int firstFlag = sqlStr.indexOf("create\\stable");
        int firstFlag = -1;
        Pattern pattern = Pattern.compile("\\bcreate\\s+table\\b");
        Matcher matcher = pattern.matcher(sqlStr);
        if (matcher.find())
            firstFlag = matcher.start();

        if (firstFlag == -1){
            return bodyList;
        }else {
            int lastFlag = sqlStr.indexOf("))");
            bodyList.add(sqlStr.substring(firstFlag,lastFlag+2));
            sqlStr = sqlStr.substring(0,firstFlag) + sqlStr.substring(lastFlag+2,sqlStr.length());
            return getBodyStrList(sqlStr,bodyList);
        }
    }

    // 读取文件，获取sql文件有效信息
    private String getSqlStr(File file) {
        StringBuilder sb = new StringBuilder();
        try {
            List<String> strings = Files.readAllLines(Paths.get(file.getPath()));
            for (String str : strings){
                sb.append(SqlUtil.removeSinglelineComments(str.trim()));
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return SqlUtil.removeMultiLineComments(sb.toString());
    }




}
